Tips & Tricks for Excel-Based Financial Modeling, Volume II by M.A. Mian

Author:M.A. Mian [M.A. Mian]
Language: eng
Format: epub
Publisher: Business Expert Press
Published: 2017-07-30T16:00:00+00:00

Year 4

137

1,376

7,000

NPV at 10%

500

2,374

1,200

6,482

5,806

Figure 2.59 Multiperiod investment optimization using Solver

5. Enter =SUM(B2:F2) in Cell G2 and copy to Cell G5.

6. Manually enter the constraints in Cells H2:H5.

7. Enter =SUMPRODUCT(B$7:F$7,B2:F2) in Cell I2 and copy it to I6.

8. Enter =SUMPRODUCT(B7:F7,B8:F8) in Cell G8.

9. Solver Parameters.

a. Set Objective: G8 → Objective is to maximize NPV in this cell.

b. To: Tick Max.

c. By Changing Variable Cells: B7:F7.

d. Subject to the Constraints:

i. B7:F7 = Binary → This is to make sure that only a whole project is selected because a partial project cannot be implemented, that is, either select the project or not.

ii. I2:I5 <=H2:H5 → This is to make sure that the each year’s budget does not exceed the constraint for each year.

e. Select a Solving Method: Simplex LP.

f. Click on Solve.

g. Click on OK.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.